﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using OestsCommon.Model;
using Utils;

namespace OestsDataBase
{
    public static class PaperDAL
    {
        //项目表 对应实训 考试
        public static void CreateTable()
        {
            string sqlCheck = "if object_id( 'T_Papers') is not null select 1 else select 0";
            if ((int)SqlHelper.ExecuteScalar(sqlCheck) == 0)
            {
                string sqlCreacte = @"CREATE TABLE T_Papers (id bigint identity(1,1),name NVARCHAR(max) not null ,time int,projectId bigint not null,teacher_id int not null)";
                SqlHelper.ExecuteNonQuery(sqlCreacte);
            }
        }

        public static Paper GetExamPaper(int paperId)
        {
            string strSql = "select * from T_Papers where id = " + paperId;
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null || dt.Rows.Count == 0) { return null; }
            DataRow dr = dt.Rows[0];
			Paper s = ReadDataFromPapers(dr);
            return s;
        }

		public static List<int> GetStudentInPaper(int paperId)
		{
			string strSql = "select student_ids from T_Papers where id = " + paperId;
			DataTable dt = SqlHelper.ExecuteDataTable(strSql);
			if (dt == null) { return null; }
            if (dt.Rows.Count == 0) return null;
			DataRow dr = dt.Rows[0];
			if(dr["student_ids"].Equals(DBNull.Value)|| dr["student_ids"].ToString()=="")
			{
				return null;
			}
			string stuentIdsStr = dr["student_ids"].ToString();
			List<int> data = stuentIdsStr.ToNList();
			return data;
		}
		public static bool ModifyStudents(int paperId, string students)
		{
			string strSql = string.Format("update T_Papers set student_ids='{0}' where id = @id", students);
			int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", paperId));
			if (n != 0)
			{

				return true;
			}
			return false;
		}

		public static List<int> GetPaperIdListByTeacherId(int teacherId)
		{
			List<int> list = new List<int>();
			string strSql = "select id from T_Papers where teacher_id = " + teacherId;
			DataTable dt = SqlHelper.ExecuteDataTable(strSql);
			if (dt == null) { return null; }
			foreach (DataRow dr in dt.Rows)
			{
				list.Add(Int32.Parse(dr["id"].ToString()));
			}
			return list;
		}

		/// <summary>
		/// 获取某个教师权限下的试卷
		/// </summary>
		/// <param name="teacherid"></param>
		/// <returns></returns>
		public static List<Paper> GetDataByTeacher(int teacherid)
        {
            List<Paper> list = new List<Paper>();
            string strSql = "select * from T_Papers where teacher_id = " + teacherid;
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);
            if (dt == null) { return null; }
            foreach (DataRow dr in dt.Rows)
            {
				Paper s = ReadDataFromPapers(dr);
                list.Add(s);
            }
            return list;
        }

        /// <summary>
        /// 从Papers表中读取一张试卷
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="projectId"></param>
        /// <returns></returns>
		private static Paper ReadDataFromPapers(DataRow dr)
		{
            try
            {
                Paper s = new Paper();
                s.Id = Int32.Parse(dr["id"].ToString());
                s.Name = dr["name"].ToString();
                s.Time = Int32.Parse(dr["time"].ToString());
                s.ProjectId = Int32.Parse(dr["projectId"].ToString());
                s.TeacherId = Int32.Parse(dr["teacher_id"].ToString());
                if (!dr["student_ids"].Equals(DBNull.Value)&& dr["student_ids"].ToString()!= "")
                {
                    string studentIdsStr = dr["student_ids"].ToString();
                    s.StudentList = studentIdsStr.ToNList();
                }

                s.Tasks = GetTaskByPaperId(s.ProjectId,s.Id);
                return s;
            }
            catch (Exception e)
            {

                throw e;
            }
			
		}

        /// <summary>
        /// 获取所有项目的操作卷
        /// </summary>
        /// <returns></returns>
        public static List<Paper> GetData()
        {
            string strSql = "select * from T_Papers";
            DataTable dt = SqlHelper.ExecuteDataTable(strSql);

            if (dt == null) { return null; }
            List<Paper> list = new List<Paper>();
            foreach (DataRow dr in dt.Rows)
            {
				Paper s = ReadDataFromPapers(dr);
				list.Add(s);
            }
            return list;
        }

        private static List<TaskModel> GetTaskByPaperId(int projectId,int paperId)
        {
            try
            {
                string strSql = "select * from T_Paper_" + paperId;
                DataTable dt = SqlHelper.ExecuteDataTable(strSql);
                if (dt == null) { return null; }

                List<TaskModel> list = new List<TaskModel>();

                foreach (DataRow dr in dt.Rows)
                {
                    int id = Int32.Parse(dr["task_id"].ToString());
                    int postId = Int32.Parse(dr["post_id"].ToString());
                    int score = Int32.Parse(dr["score"].ToString());
                    TaskModel t = TaskDAL.GetTaskById(projectId, id);
                    t.Score = score;
                    t.ExamPostId = postId;
                    list.Add(t);
                }
                return list;
            }
            catch (Exception e)
            {

                throw e;
            }
       
        }

        /// <summary>
        /// 添加一张试卷
        /// </summary>
        /// <param name="name">试卷名称</param>
        /// <param name="time">试卷时间</param>
        /// <param name="teacherId">教师id</param>
        /// <returns></returns>
        public static int AddPaper(string name, int time, int postid, int teacherId)
        {
            if (name == "") return -1;
            string strSql = string.Format(@"insert into T_Papers (name,time,projectId,teacher_id)values('{0}',{1},{2},{3});SELECT @@Identity", name, time, postid, teacherId);
            object obj = SqlHelper.ExecuteScalar(strSql);

            int nID = Int32.Parse(obj.ToString());
            //创建一张试卷表
            CreatPaperTable(nID);
            return nID;
        }


		/// <summary>
		/// 创建实训组的表
		/// </summary>
		/// <param name="id"></param>
		private static void CreatPaperTable(int id)
        {
            string sqlCheck = string.Format("if object_id('{0}') is not null select 1 else select 0", "T_Paper_" + id);
            if ((int)SqlHelper.ExecuteScalar(sqlCheck) == 0)
            {
                string sqlCreate = @"CREATE TABLE T_Paper_" + id + @" (task_id bigint  primary key,post_id int not null, score int)";
                SqlHelper.ExecuteNonQuery(sqlCreate);
            }
            else
            {
                //清空已有数据，并且将自增自段恢复从1开始计数 
                string sql = "truncate table T_Paper_" + id;
                SqlHelper.ExecuteNonQuery(sql);
            }
        }

        /// <summary>
        /// 修改 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool ModifyPaperName(int id, string name = null)
        {
            string strSql = string.Format("update T_Papers set name='{0}' where id = @id", name);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", id));
            if (n != 0)
            {
                return true;
            }
            return false;
        }



        /// <summary>
        /// 删除实训组  
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool DelPaper(int id)
        {
            string strSql = string.Format("delete from  T_Papers  where id = @id");
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", id));
            if (n != 0)
            {
                // 删除对应表
                string sqlCheck = string.Format("if object_id('{0}') is not null select 1 else select 0", "T_Paper_" + id);
                if ((int)SqlHelper.ExecuteScalar(sqlCheck) == 1)
                {
                    string sqlCreate = @"drop TABLE T_Paper_" + id;
                    SqlHelper.ExecuteNonQuery(sqlCreate);
                }
                return true;
            }
            return false;
        }

        /// <summary>
        /// 修改 
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static bool ModifyExamInfo(int id, string examName, int time)
        {

            string strSql = string.Format("update T_Papers set name='{0}',time='{1}' where id = @id", examName, time);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", id));
            if (n != 0)
            {
                return true;
            }
            return false;
        }

        public static bool ModifyPaperTime(int id, int time)
        {
            string strSql = string.Format("update T_Papers set time='{0}' where id = @id", time);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", id));
            if (n != 0)
            {

                return true;
            }
            return false;
        }

        /// <summary>
        /// 添加任务到试卷
        /// </summary>
        /// <param name="paperId"></param>
        /// <param name="taskId"></param>
        public static void AddTaskToPaper(int paperId, int taskId,int score, int postId=0)
        {
            string strSql = string.Format(@"insert into T_Paper_" + paperId + " (task_id,post_id,score)values({0},{1},{2})", taskId, postId, score);
            SqlHelper.ExecuteScalar(strSql);
        }


        /// <summary>
        /// 修改试卷中的考试岗位
        /// </summary>
        /// <param name="id"></param>
        /// <param name="post"></param>
        /// <returns></returns>
        public static bool ModifyExamPostInPaper(int paperId, int taskId, int post)
        {
            string strSql = string.Format("update T_Paper_" + paperId + " set post_id={0} where task_id = @id", post);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", taskId));
            if (n != 0)
            {
                return true;
            }
            return false;
        }
        public static bool ModifyExamScore(int paperId, int taskId, int score)
        {
            string strSql = string.Format("update T_Paper_" + paperId + " set score={0} where task_id = @id", score);
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", taskId));
            if (n != 0)
            {
                return true;
            }
            return false;
        }

        public static bool DelTaskInPaper(int paperId, int taskId)
        {
            string strSql = string.Format("delete from  T_Paper_" + paperId + "  where task_id = @id");
            int n = SqlHelper.ExecuteNonQuery(strSql, new SqlParameter("@id", taskId));
            if (n != 0)
            {
                return true;
            }
            return false;
        }
    }
}
